
package com.vanda.report;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;

import jxl.Workbook;
import jxl.write.WritableWorkbook;

import org.apache.commons.validator.GenericValidator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import com.vanda.util.VandaSqlDateConverter;

/**
 * Excel操作工具类<br>
 * @author 王瑛<br>
 * @version Version 1.00<br>
 */
public class ExcelUtils {
	
	/**
	 * 删除Sheet行
	 * @param sheet 表单
	 * @param start 开始行数
	 * @param end 结束行数
	 */
	public static void removeRow(HSSFSheet sheet, int start, int end) {
		// 把范围内的合并单元格删除掉
		int numRegions = sheet.getNumMergedRegions();
		for (int j = numRegions - 1; j >= 0; j--) {
			Region r = sheet.getMergedRegionAt(j);
			if (r.getRowFrom() >= start && r.getRowTo() <= end)
				sheet.removeMergedRegion(j);
		}
		for (int i = end; i >= start; i--) {
			HSSFRow row = sheet.getRow(i);
			if (row != null)
				sheet.removeRow(row);
		}
	}
	
	/**
	 * 返回对应的cell
	 * @param sheet 表单
	 * @param rowNum 行数
	 * @param colNum 列数
	 * @return
	 */
	public static HSSFCell getCell(HSSFSheet sheet, int rowNum, int colNum) {
		HSSFRow row = sheet.getRow(rowNum);
		if (row == null) {
			row = sheet.createRow(rowNum);
		}
		HSSFCell cell = row.getCell((short) colNum);
		if (cell == null) {
			cell = row.createCell((short) colNum);
		}

		return cell;
	}
	
	/**
	 * 返回对应的cell
	 * @param sheet 表单
	 * @param cell 例：A10
	 * @return
	 */
	public static HSSFCell getCell(HSSFSheet sheet, String cell) {
		cell = cell.toUpperCase();
		
		int rowNum = 0;
		int colNum = 0;
		
		int index = 0;
		for(int i=0;i<cell.length();i++){
			char ch = cell.charAt(i);
			if(ch >= '0' && ch <= '9'){
				index = i;
				break;
			}
		}

		String colName = cell.substring(0,index);
		colNum = (int)ExcelUtils.getColIndex(colName) - 1;
		rowNum = Integer.parseInt(cell.substring(index)) - 1;
		

		return getCell(sheet,rowNum,colNum);
	}
	

	/**
	 * 设置cell数据
	 * @param cell 单元格
	 * @param cellValue 数据
	 * @param format 数据格式
	 */
	public static void setCellValue(
		HSSFCell cell,
		Object cellValue,
		String format) {
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		if (format != null && format.equals("date")) {
			if (!GenericValidator.isBlankOrNull(cellValue.toString())) {
				//转成日期格式
				VandaSqlDateConverter converter =
					new VandaSqlDateConverter(null);
				Date dtDate =
					(Date) converter.convert(null, cellValue.toString());
				if (dtDate != null)
					cell.setCellValue(dtDate);
				return;
			}
		}
		if (format != null && format.equals("number")) {
			if (!GenericValidator.isBlankOrNull(cellValue.toString())
				&& GenericValidator.isDouble(cellValue.toString())) {
				cell.setCellValue(
					(new Double(cellValue.toString())).doubleValue());
				return;
			}
		}
		//设置单元格的值
		if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC
			&& GenericValidator.isDouble(cellValue.toString())) {
			cell.setCellValue((new Double(cellValue.toString())).doubleValue());
		} else {
			cell.setCellValue(cellValue.toString());
		}
	}

	/**
	 * 删除workbook多余的sheet
	 * (调用jxl删除sheet,因为需要在poi和jxl做转换,poi删除后格式会乱)
	 * 修改：2006-2-21，改回用POI删除，以节省内存和CPU时间
	 * @param workbook
	 * @param hmSheet 需保留的sheet名
	 * @return
	 * @throws ReportException
	 */
	public static HSSFWorkbook removeSheet(
		HSSFWorkbook workbook,
		HashMap hmSheet)
		throws ReportException {

		if (hmSheet.size() == 0) {
			return workbook;
		}

		try {
			//检查哪些sheet是生成的
			for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
				String sheetName = workbook.getSheetName(i);
				//如果不是生成的sheet,则删掉
				if (hmSheet.get(sheetName) == null) {
					workbook.removeSheetAt(i);
					i--;
				}
			}
			return workbook;
		} catch (Exception e) {
			e.printStackTrace();
			throw new ReportException("Remove Sheet Failed!", e);
		}

		//		ByteArrayOutputStream os = null;
		//		ByteArrayInputStream is = null;
		//
		//		try {
		//			os = new ByteArrayOutputStream();
		//			workbook.write(os);
		//			byte[] array = os.toByteArray();
		//			os.reset();
		//			os.close();
		//
		//			//转给jxl,删除sheet
		//			is = new ByteArrayInputStream(array);
		//			Workbook old = Workbook.getWorkbook(is);
		//			is.reset();
		//			is.close();
		//
		//			os = new ByteArrayOutputStream();
		//			WritableWorkbook newWb = Workbook.createWorkbook(os, old);
		//
		//			//检查哪些sheet是生成的
		//			List lstIndex = new ArrayList();
		//			for (int i = 0; i < newWb.getNumberOfSheets(); i++) {
		//				WritableSheet sheet = newWb.getSheet(i);
		//				String sheetName = sheet.getName();
		//				//如果不是生成的sheet,则删掉
		//				if (hmSheet.get(sheetName) == null) {
		//					// TODO remove操作后，POI读取有时出错，先改为hidden
		//					sheet.setHidden(true);
		//					lstIndex.add(new Integer(i));
		//					//					newWb.removeSheet(i);
		//					//					i--;
		//				}
		//			}
		//
		//			for (int i = 0; i < lstIndex.size(); i++) {
		//				int index = ((Integer) lstIndex.get(i)).intValue();
		//				newWb.moveSheet(index, newWb.getNumberOfSheets() - 1);
		//			}
		//
		//			newWb.write();
		//			newWb.close();
		//
		//			array = os.toByteArray();
		//			os.reset();
		//			os.close();
		//
		//			is = new ByteArrayInputStream(array);
		//			workbook = new HSSFWorkbook(is);
		//			is.reset();
		//			is.close();
		//
		//		} catch (Exception e) {
		//			e.printStackTrace();
		//			throw new ReportException("Remove Sheet Failed!", e);
		//		} finally {
		//			try {
		//				if (os != null) {
		//					os.close();
		//				}
		//			} catch (Exception e) {
		//			}
		//			try {
		//				if (is != null) {
		//					is.close();
		//				}
		//			} catch (Exception e) {
		//			}
		//		}
		//		return workbook;
	}

	/**
	 * 克隆Sheet
	 * @param workbook Excel工作本
	 * @param sheetIndex Sheet位置
	 * @param name 克隆后Sheet名称
	 * @return
	 */
	public static HSSFWorkbook cloneSheet(
		HSSFWorkbook workbook,
		int sheetIndex,
		String name)
		throws ReportException {

		ByteArrayOutputStream os = null;
		ByteArrayInputStream is = null;
		try {
			//创建一个输出流,将workbook写入此流
			os = new ByteArrayOutputStream();
			workbook.write(os);
			//创建一个输入流
			is = new ByteArrayInputStream(os.toByteArray());
			os.close();

			//
			os = new ByteArrayOutputStream();
			Workbook wb = Workbook.getWorkbook(is);
			WritableWorkbook copy = Workbook.createWorkbook(os, wb);
			//clone sheet,并将clone后的sheet放到最后
			copy.copySheet(sheetIndex, name, wb.getSheets().length);
			//关闭workbook
			copy.write();
			copy.close();

			is.close();
			is = new ByteArrayInputStream(os.toByteArray());
			workbook = new HSSFWorkbook(is);

		} catch (Exception e) {
			e.printStackTrace();
			throw new ReportException("Clone Failed!", e);
		} finally {
			try {
				if (os != null) {
					os.close();
				}
			} catch (Exception e) {
			}
			try {
				if (is != null) {
					is.close();
				}
			} catch (Exception e) {
			}
		}

		return workbook;
	}

	/**
	 * 从源Sheet拷贝指定内容到目标Sheet
	 * @param sourceSheet 源Sheet
	 * @param targetSheet 目标Sheet
	 * @param pStartRow 开始行数
	 * @param pEndRow 结束行数 
	 * @param pPosition 目标Sheet位置
	 */
	public static void copyRows(
		HSSFSheet sourceSheet,
		HSSFSheet targetSheet,
		int pStartRow,
		int pEndRow,
		int pPosition) {
		//拷贝内容
		copyRows(
			sourceSheet,
			targetSheet,
			pStartRow,
			pEndRow,
			pPosition,
			true,
			false);
	}

	/**
	 * 从源Sheet拷贝指定内容到目标Sheet
	 * @param sourceSheet 源Sheet
	 * @param targetSheet 目标Sheet
	 * @param pStartRow 开始行数
	 * @param pEndRow 结束行数 
	 * @param pPosition 目标Sheet位置
	 * @param isCopyValue 是否拷贝值
	 * @param wrap 设置行高
	 */
	public static void copyRows(
		HSSFSheet sourceSheet,
		HSSFSheet targetSheet,
		int pStartRow,
		int pEndRow,
		int pPosition,
		boolean isCopyValue,
		boolean wrap) {
		HSSFRow sourceRow = null;
		HSSFRow targetRow = null;
		HSSFCell sourceCell = null;
		HSSFCell targetCell = null;
		Region region = null;
		int cType;
		int i;
		short j;
		int targetRowFrom;
		int targetRowTo;
		if ((pStartRow == -1) || (pEndRow == -1)) {
			return;
		}
		//拷贝合并的单元格
		for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			region = sourceSheet.getMergedRegionAt(i);
			if ((region.getRowFrom() >= pStartRow)
				&& (region.getRowTo() <= pEndRow)) {
				targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
				targetRowTo = region.getRowTo() - pStartRow + pPosition;
				region.setRowFrom(targetRowFrom);
				region.setRowTo(targetRowTo);
				targetSheet.addMergedRegion(region);
			}
		}
		//设置列宽--对付款计划报表不可用，因为每行列宽不是都相同
		/*for (i = pStartRow; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow != null) {
				for (j = sourceRow.getFirstCellNum();
					j < sourceRow.getLastCellNum();
					j++) {
					targetSheet.setColumnWidth(
						j,
						sourceSheet.getColumnWidth(j));
				}
				break;
			}
		}*/
		//拷贝行并填充数据
		for (i = pStartRow; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow == null) {
				continue;
			}
			targetRow = targetSheet.createRow(i - pStartRow + pPosition);
			//设置行高
			if (!wrap) {
				targetRow.setHeight(sourceRow.getHeight());
			}
			for (j = sourceRow.getFirstCellNum();
				j < sourceRow.getLastCellNum();
				j++) {
				sourceCell = sourceRow.getCell(j);
				if (sourceCell == null) {
					continue;
				}
				targetCell = targetRow.createCell(j);
				targetCell.setEncoding(sourceCell.getEncoding());
				targetCell.setCellStyle(sourceCell.getCellStyle());
				cType = sourceCell.getCellType();
				targetCell.setCellType(cType);

				if (isCopyValue) {
					switch (cType) {
						case HSSFCell.CELL_TYPE_BOOLEAN :
							targetCell.setCellValue(
								sourceCell.getBooleanCellValue());
							break;
						case HSSFCell.CELL_TYPE_ERROR :
							targetCell.setCellErrorValue(
								sourceCell.getErrorCellValue());
							break;
						case HSSFCell.CELL_TYPE_FORMULA :
							//parseFormula这个函数的用途在后面说明
							targetCell.setCellFormula(
								parseFormula(sourceCell.getCellFormula()));
							break;
						case HSSFCell.CELL_TYPE_NUMERIC :
							targetCell.setCellValue(
								sourceCell.getNumericCellValue());
							break;
						case HSSFCell.CELL_TYPE_STRING :
							targetCell.setCellValue(
								sourceCell.getStringCellValue());
							break;
					}
				}
			}
		}
	}
	
	/**
	 * 处理公式，目前没有用过
	 * @param pPOIFormula
	 * @return
	 */
	private static String parseFormula(String pPOIFormula) {
		final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
		StringBuffer result = null;
		int index;
		result = new StringBuffer();
		index = pPOIFormula.indexOf(cstReplaceString);
		if (index >= 0) {
			result.append(pPOIFormula.substring(0, index));
			result.append(
				pPOIFormula.substring(index + cstReplaceString.length()));
		} else {
			result.append(pPOIFormula);
		}
		return result.toString();
	}

	/**
	 * 返回Excle列索引
	 * @param colIndex 列名称
	 * @return 列索引
	 */
	public static short getColIndex(String colIndex) {
		int _rtn = 0;
		char[] _cs = colIndex.toUpperCase().toCharArray();
		for (int i = 0; i < _cs.length; i++) {
			_rtn = _rtn * 26 + (_cs[i] - 'A' + 1);
		}
		return (short) _rtn;
	}
	
	/**
	 * 将Excel文件中的列索引号转换为列名
	 * @param colIndex 列索引
	 * @return
	 */
	public static String getColName(int colIndex) {
		StringBuffer colStr = new StringBuffer();
		int a = colIndex / 26;
		int b = 0;
		if (a > 0) {
			colStr.append((char) ('A' + a - 1));
			b = colIndex % 26;
			colStr.append((char) ('A' + b));
		} else {
			b = colIndex % 26;
			colStr.append((char) ('A' + b));
		}
		return colStr.toString();
	}

	/**
	 * 验证此行是否为空行，即没有值
	 * @param row 行
	 * @return true-空行，false-非空行
	 */
	public static boolean isBlankRow(HSSFRow row) {

		Iterator iterate = row.cellIterator();
		while (iterate.hasNext()) {
			HSSFCell cell = (HSSFCell) iterate.next();

			String strTemp = getStringCellValue(cell);

			if (!GenericValidator.isBlankOrNull(strTemp)) {
				return false;
			}
		}
		return true;
	}

	/**
	 * 从cell中获得值，转换为字符串
	 * @param cell
	 * @return
	 * 
	 **/
	public static String getStringCellValue(HSSFCell cell) {
		if (null == cell)
			return "";
		String str = "";
		int type = cell.getCellType();
		//CELL_TYPE_STRING, CELL_TYPE_NUMERIC, CELL_TYPE_FORMULA,
		//CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR
		if (type == HSSFCell.CELL_TYPE_NUMERIC) {
			str = String.valueOf(cell.getNumericCellValue());
			
		} else if (type == HSSFCell.CELL_TYPE_STRING) {
			str = cell.getStringCellValue();
		}
		str = str.trim();
		return str;
	}
	
	/**
	 * 取得Cell值，并转换成BigDecimal格式
	 * @param sheet 表单
	 * @param cellName 单元格名称
	 * @return
	 */
	public static BigDecimal getNumberCellValue(HSSFSheet sheet,String cellName){
		HSSFCell cell = ExcelUtils.getCell(sheet,cellName);
		
		String value = getStringCellValue(cell);
		if(GenericValidator.isBlankOrNull(value)){
			value = "0";
		}
		
		return new BigDecimal(value);
	}
	
	/**
	 * 取得Cell值
	 * @param sheet 表单
	 * @param cellName 单元格名称
	 * @return
	 */
	public static Object getCellValue(HSSFSheet sheet,String cellName){
		HSSFCell cell = ExcelUtils.getCell(sheet,cellName);
		
		String value = getStringCellValue(cell);
		
		return value;
	}

	/**
	 * 验证单元格是否为空行，即没有值
	 * @param cell 单元格
	 * @return
	 */
	public static boolean isBlankCell(HSSFCell cell) {
		if (cell == null) {
			return true;
		} else {
			String strTemp = ExcelUtils.getStringCellValue(cell);
			if (GenericValidator.isBlankOrNull(strTemp.trim())) {
				return true;
			}
		}
		return false;
	}
}
